package cn.edu.sdjzu.xg.eagle.dao;


import cn.edu.sdjzu.xg.eagle.Service.SchoolDaoService;
import cn.edu.sdjzu.xg.eagle.entity.Pagination;
import cn.edu.sdjzu.xg.eagle.entity.School;
import cn.edu.sdjzu.xg.eagle.exception.BaseException;
import cn.edu.sdjzu.xg.eagle.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.TreeSet;

/**
 * @author tg.si@188.com
 * @project xg22
 * @date 2025/3/15
 */
public class SchoolDaoImpl implements SchoolDaoService {

    private static SchoolDaoImpl schoolDaoImpl = new SchoolDaoImpl();

    private SchoolDaoImpl() {}

    public static SchoolDaoImpl getInstance() {
        return schoolDaoImpl;
    }


    @Override
    public School findSchool(Long id) throws SQLException, BaseException {
        //创建School类型的引用，暂不创建对象
        School desiredSchool = null;
        //获得数据库连接
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "SELECT * FROM bysj.school WHERE id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setLong(1, id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        if (resultSet_select_id.next()) {
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            desiredSchool = new School(id, description, no, remarks);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet_select_id, pstmt_select_id, connection);
        return desiredSchool;
    }

    public Collection<School> findSchoolsWithPagination(Pagination pagination) throws SQLException {
        Collection<School> desiredSchools = new HashSet<School>();
        Connection connection = JdbcHelper.getConn();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM bysj.school ");
        int totalNum=CommonDao.getInstance().countAll(connection, "School");
        if(pagination!=null){
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }
        PreparedStatement pstmt_select_all = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt_select_all.executeQuery();
        while (resultSet.next()) {
            Long id = resultSet.getLong("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            School desiredSchool = new School(id, description, no, remarks);
            desiredSchools.add(desiredSchool);
        }
        JdbcHelper.close(resultSet, pstmt_select_all, connection);
        return desiredSchools;
    }


    @Override
    public Collection<School> findSchools() throws SQLException, BaseException {
        //创建集合类对象，用来保存所有记录代表的School对象
        //TreeSet按compare(Object o)方法指定的顺序排序
        Collection<School> desiredSchools = new TreeSet<School>();
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();

        //创建查询School的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM School ");

        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = pstmt_select_all.executeQuery();
        //遍历resultSet
        while (resultSet.next()) {
            //获得school表中当前记录的id字段的值
            Long id = resultSet.getLong("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            //以当前记录的各个字段值为参数，创建School对象
            School desiredSchool = new School(id, description, no, remarks);
            //将当前记录代表的School对象添加到desiredSchools中
            desiredSchools.add(desiredSchool);
        }
        JdbcHelper.close(resultSet, pstmt_select_all, connection);
        return desiredSchools;
    }

    @Override
    public boolean updateSchool(Connection connection, School school) throws SQLException {

        //准备预编译的语句
        String sql_update = "UPDATE school SET description = ?, no = ?, remarks = ? WHERE id = ?";
        PreparedStatement pstmt_update =
                connection.prepareStatement(sql_update);

        pstmt_update.setString(1, school.getDescription());
        pstmt_update.setString(2, school.getNo());
        pstmt_update.setString(3, school.getRemarks());
        pstmt_update.setLong(4, school.getId());

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();

        JdbcHelper.close(pstmt_update, connection);
        return rowAffected > 0;
    }
    @Override
    public boolean addSchool(Connection connection, School school) throws SQLException {
        String sql_insert = "INSERT INTO school (description, no, remarks) VALUES (?, ?, ?)";
        // 使用 RETURN_GENERATED_KEYS 获取自增主键
        try (PreparedStatement pstmt = connection.prepareStatement(sql_insert, PreparedStatement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, school.getDescription());
            pstmt.setString(2, school.getNo());
            pstmt.setString(3, school.getRemarks());

            int rowAffected = pstmt.executeUpdate();

            // 获取并设置自增主键
            if (rowAffected > 0) {
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        school.setId(generatedKeys.getLong(1)); // 关键修复
                    }
                }
            }
            return rowAffected > 0;
        }
    }

    @Override
    public boolean deleteSchool(Long id) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_delete_id = "DELETE FROM school WHERE id = ?";
        PreparedStatement pstmt_delete =
                connection.prepareStatement(sql_delete_id);

        pstmt_delete.setLong(1, id);

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_delete.executeUpdate();

        JdbcHelper.close(pstmt_delete, connection);
        return rowAffected > 0;
    }

    public boolean deleteSchool(School school) throws SQLException {
        return this.deleteSchool(school.getId());
    }

}
